---
redirect_from:
  - /config/databases/clickhouse
---

# ClickHouse

[ClickHouse](https://clickhouse.com) is a fast and resource efficient
[open-source database](https://github.com/ClickHouse/ClickHouse) for real-time
applications and analytics.

## Prerequisites

- The hostname for the [ClickHouse][clickhouse] database server
- The [username/password][clickhouse-docs-users] for the
  [ClickHouse][clickhouse] database server

## Setup

### Manual

Add the following to a `.env` file in your Cube project:

```dotenv
CUBEJS_DB_TYPE=clickhouse
CUBEJS_DB_HOST=my.clickhouse.host
CUBEJS_DB_NAME=my_clickhouse_database
CUBEJS_DB_USER=clickhouse_user
CUBEJS_DB_PASS=**********
```

## Environment Variables

| Environment Variable            | Description                                                                         | Possible Values           | Required |
| ------------------------------- | ----------------------------------------------------------------------------------- | ------------------------- | :------: |
| `CUBEJS_DB_HOST`                | The host URL for a database                                                         | A valid database host URL |    ✅    |
| `CUBEJS_DB_PORT`                | The port for the database connection                                                | A valid port number       |    ❌    |
| `CUBEJS_DB_NAME`                | The name of the database to connect to                                              | A valid database name     |    ✅    |
| `CUBEJS_DB_USER`                | The username used to connect to the database                                        | A valid database username |    ✅    |
| `CUBEJS_DB_PASS`                | The password used to connect to the database                                        | A valid database password |    ✅    |
| `CUBEJS_DB_CLICKHOUSE_READONLY` | Whether the ClickHouse user has read-only access or not                             | `true`, `false`           |    ❌    |
| `CUBEJS_CONCURRENCY`            | The number of concurrent connections each queue has to the database. Default is `5` | A valid number            |    ❌    |
| `CUBEJS_DB_MAX_POOL`            | The maximum number of concurrent database connections to pool. Default is `20`      | A valid number            |    ❌    |

## Pre-Aggregation Feature Support

When using [pre-aggregations][ref-preaggs] with ClickHouse, you have to define
[indexes][ref-preaggs-indexes] in pre-aggregations. Otherwise, you might get
the following error: `ClickHouse doesn't support pre-aggregations without indexes`.

### `count_distinct_approx`

Measures of type
[`count_distinct_approx`][ref-schema-ref-types-formats-countdistinctapprox] can
not be used in pre-aggregations when using ClickHouse as a source database.

### `rollup_join`

You can use [`rollup_join` pre-aggregations][ref-preaggs-rollup-join] to join
data from ClickHouse and other data sources inside Cube Store.

Alternatively, you can leverage ClickHouse support for [integration table
engines](https://clickhouse.com/docs/en/engines/table-engines#integration-engines)
to join data from ClickHouse and other data sources inside ClickHouse.
To do so, define table engines in ClickHouse and connect your ClickHouse as the
only data source to Cube.

## Pre-Aggregation Build Strategies

<InfoBox>

To learn more about pre-aggregation build strategies, [head
here][ref-caching-using-preaggs-build-strats].

</InfoBox>

| Feature       | Works with read-only mode? | Is default? |
| ------------- | :------------------------: | :---------: |
| Batching      |             ✅             |     ✅      |
| Export Bucket |             ✅             |      -      |

By default, ClickHouse uses [batching][self-preaggs-batching] to build
pre-aggregations.

### Batching

No extra configuration is required to configure batching for ClickHouse.

### Export Bucket

<WarningBox>

  Clickhouse driver **only** supports using AWS S3 for export buckets.

</WarningBox>

#### AWS S3

For [improved pre-aggregation performance with large
datasets][ref-caching-large-preaggs], enable export bucket functionality by
configuring Cube with the following environment variables:

<InfoBox>

  Ensure the AWS credentials are correctly configured in IAM to allow reads and
  writes to the export bucket in S3.

</InfoBox>

```dotenv
CUBEJS_DB_EXPORT_BUCKET_TYPE=s3
CUBEJS_DB_EXPORT_BUCKET=my.bucket.on.s3
CUBEJS_DB_EXPORT_BUCKET_AWS_KEY=<AWS_KEY>
CUBEJS_DB_EXPORT_BUCKET_AWS_SECRET=<AWS_SECRET>
CUBEJS_DB_EXPORT_BUCKET_AWS_REGION=<AWS_REGION>
```

## SSL

To enable SSL-encrypted connections between Cube and ClickHouse, set the
`CUBEJS_DB_SSL` environment variable to `true`. For more information on how to
configure custom certificates, please check out [Enable SSL Connections to the
Database][ref-recipe-enable-ssl].

## Additional Configuration

You can connect to a ClickHouse database when your user's permissions are
[restricted][clickhouse-readonly] to read-only, by setting
`CUBEJS_DB_CLICKHOUSE_READONLY` to `true`.

[clickhouse]: https://clickhouse.tech/
[clickhouse-docs-users]:
  https://clickhouse.tech/docs/en/operations/settings/settings-users/
[clickhouse-readonly]: https://clickhouse.com/docs/en/operations/settings/permissions-for-queries#readonly
[ref-caching-using-preaggs-build-strats]:
  /product/caching/using-pre-aggregations#pre-aggregation-build-strategies
[ref-recipe-enable-ssl]:
  /guides/recipes/data-sources/using-ssl-connections-to-data-source
[ref-caching-large-preaggs]:
  /product/caching/using-pre-aggregations#export-bucket
[ref-schema-ref-types-formats-countdistinctapprox]: /reference/data-model/types-and-formats#count_distinct_approx
[self-preaggs-batching]: #batching
[ref-preaggs]: /product/caching/using-pre-aggregations
[ref-preaggs-indexes]: /reference/data-model/pre-aggregations#indexes
[ref-preaggs-rollup-join]: /reference/data-model/pre-aggregations#rollup_join